﻿Imports Microsoft.VisualBasic

Public Class requisition

    'membros ou campos
    Public id_requisition As Integer
    Public id_item As Integer
    Public id_user As Integer
    Public date_rec As Date
    Public delivered As Date

    'Nascimento da Variavel, ou seja o construtor
    Public Sub New()
        id_requisition = 0
        id_item = 0
        id_user = 0
        date_rec = Now
        delivered = Now

    End Sub


    Public Property id_request_() As String
        Get
            Return id_requisition
        End Get
        Set(ByVal value As String)
            id_requisition = value
        End Set
    End Property

    Function insert() As String
        'se inseriu bem na base de dados
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        'Dim string_sql As String = "INSERT INTO requisitions(id_item,id_user,date_rec,delivered) VALUES(1,1,GETDATE(),GETDATE());"
        Dim string_sql As String = "INSERT INTO requisitions(id_item,id_user,date_rec,delivered) VALUES('" + id_item.ToString() + "','" + id_user.ToString() + "',getdate(),getdate());"
        SqlCommand1.Connection = SqlConnection1
        SqlCommand1.CommandText = string_sql
        SqlCommand1.ExecuteNonQuery()
        SqlConnection1.Close()

        Return string_sql

    End Function
    Function update() As String
        'se inseriu bem na base de dados
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        Dim string_sql As String = "UPDATE requisitions set id_item= " + id_item.ToString() + ",id_user= " + id_user.ToString() + " ,date_rec= getdate()  ,delivered= getdate()  where id_requisition= " + id_requisition.ToString() + ""
        SqlCommand1.Connection = SqlConnection1
        SqlCommand1.CommandText = string_sql
        SqlCommand1.ExecuteNonQuery()
        SqlConnection1.Close()

        Return string_sql

    End Function
    Function first() As String
        'se inseriu bem na base de dados
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        Dim string_sql As String = "Select top 1 id_requisition,id_item,id_user,date_rec,delivered from requisitions order by id_requisition ASC"
        SqlCommand1.Connection = SqlConnection1
        SqlCommand1.CommandText = string_sql

        Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader

        SqlDataReader1 = SqlCommand1.ExecuteReader()
        If SqlDataReader1.Read() Then
            id_requisition = SqlDataReader1.GetInt32(0)
            id_item = SqlDataReader1.GetInt32(1)
            id_user = SqlDataReader1.GetInt32(2)
            date_rec = SqlDataReader1.GetDateTime(3)
            delivered = SqlDataReader1.GetDateTime(4)
        End If


        SqlConnection1.Close()

        Return string_sql

    End Function

    Function last() As String
        'se inseriu bem na base de dados
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        Dim string_sql As String = "Select top 1 id_requisition,id_item,id_user,date_rec,delivered from requisitions order by id_requisition DESC"
        SqlCommand1.Connection = SqlConnection1
        SqlCommand1.CommandText = string_sql

        Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader

        SqlDataReader1 = SqlCommand1.ExecuteReader()
        If SqlDataReader1.Read() Then
            id_requisition = SqlDataReader1.GetInt32(0)
            id_item = SqlDataReader1.GetInt32(1)
            id_user = SqlDataReader1.GetInt32(2)
            date_rec = SqlDataReader1.GetDateTime(3)
            delivered = SqlDataReader1.GetDateTime(4)
        End If


        SqlConnection1.Close()

        Return string_sql

    End Function

    Function nextt() As String
        'se inseriu bem na base de dados
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        Dim string_sql As String = "Select top 1 id_requisition,id_item,id_user,date_rec,delivered from requisitions where id_requisition > " + id_requisition.ToString() + " order by id_requisition ASC"
        SqlCommand1.Connection = SqlConnection1
        SqlCommand1.CommandText = string_sql

        Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader

        SqlDataReader1 = SqlCommand1.ExecuteReader()
        If SqlDataReader1.Read() Then
            id_requisition = SqlDataReader1.GetInt32(0)
            id_item = SqlDataReader1.GetInt32(1)
            id_user = SqlDataReader1.GetInt32(2)
            date_rec = SqlDataReader1.GetDateTime(3)
            delivered = SqlDataReader1.GetDateTime(4)
        End If


        SqlConnection1.Close()

        Return string_sql

    End Function

    Function previous(ByVal id_req As String) As String
        'se inseriu bem na base de dados
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        Dim string_sql As String = "Select top 1 id_requisition,id_item,id_user,date_rec,delivered from requisitions where id_requisition < " + id_req + " order by id_requisition DESC"
        SqlCommand1.Connection = SqlConnection1
        SqlCommand1.CommandText = string_sql

        Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader

        SqlDataReader1 = SqlCommand1.ExecuteReader()
        If SqlDataReader1.Read() Then
            id_requisition = SqlDataReader1.GetInt32(0)
            id_item = SqlDataReader1.GetInt32(1)
            id_user = SqlDataReader1.GetInt32(2)
            date_rec = SqlDataReader1.GetDateTime(3)
            delivered = SqlDataReader1.GetDateTime(4)
        End If


        SqlConnection1.Close()

        Return string_sql

    End Function
    Function delete() As String
        'se inseriu bem na base de dados
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        Dim string_sql As String = "Delete from requisitions where id_requisition = " + id_requisition.ToString() + ""
        SqlCommand1.Connection = SqlConnection1
        SqlCommand1.CommandText = string_sql

        Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader

        SqlDataReader1 = SqlCommand1.ExecuteReader()
        If SqlDataReader1.Read() Then
            id_requisition = SqlDataReader1.GetInt32(0)
            id_item = SqlDataReader1.GetInt32(1)
            id_user = SqlDataReader1.GetInt32(2)
            date_rec = SqlDataReader1.GetDateTime(3)
            delivered = SqlDataReader1.GetDateTime(4)
        End If


        SqlConnection1.Close()

        Return string_sql

    End Function

End Class
